﻿using System;
using System.Collections.Generic;
using System.Linq;
using System.Text;
using System.Threading.Tasks;
using System.Data.SqlClient;
using System.Data;
using System.IO;
using System.Windows.Forms;
using SqlSugar;
using DbType = SqlSugar.DbType;

namespace WindowsFormsApp1
{
    class sqlHelper
    {
        //创建连接字符串，和sql server数据库的固定写法
        public static ConnectionConfig connectionConfigSqlserver = new ConnectionConfig()
        {
            ConnectionString = @"server=PC-20230509OHUX;database=test;pwd=123;uid=sa",
            IsAutoCloseConnection = true,
            DbType = DbType.SqlServer
        };

        public static ConnectionConfig connectionConfigMysql = new ConnectionConfig()
        {
            ConnectionString = @"server=PC-20230509OHUX/SQL22;database=Test;password=123;user id=sa",
            IsAutoCloseConnection = true,
            DbType = DbType.MySql
        };

        public static ConnectionConfig connectionConfigSqlite = new ConnectionConfig()
        {
            ConnectionString = @"data source=d:/123.db",
            IsAutoCloseConnection = true,
            DbType = DbType.Sqlite
        };

        /// <summary>
        /// DB-first--根据表生成实体类
        /// 先有表，根据表来创建类
        /// </summary>
        public static void CreateClass()
        {
            using (SqlSugarClient db = new SqlSugarClient(connectionConfigSqlserver))
            {
                try
                {
                    DirectoryInfo di = new DirectoryInfo(AppDomain.CurrentDomain.BaseDirectory);
                    //获取当前文件的根目录，即获取bin目录下的文件
                    string path = di.Parent.Parent.FullName;
                    //类文件放在bin目录的上层目录的上层目录
                    db.DbFirst.IsCreateAttribute().CreateClassFile(path, "WindowsFormsApp1");
                    // db.DbFirst表示先有数据库，CreateClassFile传入的参数是路径和namespace
                }
                catch (Exception ex)
                {
                    MessageBox.Show(ex.ToString());
                }
            }
        }


        /// <summary>
        /// code-first--根据实体类生成表
        /// </summary>
        public static void CreateTable()
        {
            using (SqlSugarClient db = new SqlSugarClient(connectionConfigSqlserver))
            {
                db.CodeFirst.InitTables(typeof(PeopleInfo));
            }

        }

        /// <summary>
        /// 重命名表
        /// </summary>
        /// <param name="s"></param>
        public static void RenameTable(string s)
        {
            using (SqlSugarClient db = new SqlSugarClient(connectionConfigSqlserver))
            {
                if (db.DbMaintenance.IsAnyTable(s))//判断表名存不存在，如果存在这个表，则new一个
                    db.DbMaintenance.RenameTable(s, "new" + s);
                else
                    MessageBox.Show("该表不存在！");
            }
        }

        /// <summary>
        /// 插入单个数据
        /// </summary>
        /// <param name="s"></param>
        public static int InsertObj(PeopleInfo peopleInfo)
        {
            int res = 0;
            using (SqlSugarClient db = new SqlSugarClient(connectionConfigSqlserver))
            {
                res = db.Insertable(peopleInfo).ExecuteCommand();//插入单行的数据
            }
            return res;
        }

        /// <summary>
        /// 插入批量数据
        /// </summary>
        /// <param name="s"></param>
        public static int InsertBatch(List<PeopleInfo> peopleInfos)
        {

            int res = 0;
            using (SqlSugarClient db = new SqlSugarClient(connectionConfigSqlserver))
            {
                db.Deleteable<PeopleInfo>().ExecuteCommand();
                res = db.Insertable(peopleInfos).ExecuteCommand();
            }
            return res;
        }

        public static int DeleteSelectedRow(string nameToDelete)
        {
            int res = 0;
            try
            {
                using (SqlSugarClient db = new SqlSugarClient(connectionConfigSqlserver))
                {
                    PeopleInfo peopleInfo = db.Queryable<PeopleInfo>().Single(x => x.Name == nameToDelete);

                    // 弹出消息框确认是否删除
                    DialogResult result = MessageBox.Show("确定要删除"+nameToDelete.ToString()+"这行数据吗？", "确认删除", MessageBoxButtons.OKCancel, MessageBoxIcon.Question);
                    if (result == DialogResult.OK)
                    {
                        res = db.Deleteable<PeopleInfo>(peopleInfo).ExecuteCommand();
                    }
                }
            }
            catch (Exception ex)
            {
                MessageBox.Show(ex.Message);
            }
            return res;
        }

    }
}
